# -*-coding:UTF-8-*-
import sys
import MySQLdb
import traceback
import time
# from logger import logger
reload(sys)
sys.setdefaultencoding('utf-8')


class MySQL:

    def __init__(self,
                 host='127.0.0.1',
                 user='root',
                 passwd='root',
                 db='xuexi',
                 port=3306,
                 charset='utf8'
                 ):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port
        self.charset = charset
        self.conn = None
        # self.logger = logger(name="MySQL")
        self._conn()
        self.cursor = None

    def _conn(self):
        try:
            self.conn = MySQLdb.Connection(self.host,
                                           self.user,
                                           self.passwd,
                                           self.db,
                                           self.port,
                                           self.charset)
            return True
        except:
            return False

    # 重试连接总次数为3次
    def _reConn(self, num=3, stime=1):
        _number = 0
        _status = True
        while _status and _number <= num:
            try:
                self.conn.ping()  # cping 校验连接是否异常
                _status = False
            except:
                if self._conn() is True:  # 重新连接,成功退出
                    _status = False
                    break
                _number += 1
                time.sleep(stime)  # 连接不成功,休眠1秒钟,继续循环，直到成功或重试次数结束

    def select(self, sql=''):
        try:
            self._reConn()
            self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
            self.cursor.execute("set names utf8")  # utf8 字符集
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.cursor.close()
            return result
        except MySQLdb.Error, e:
            # self.logger.error("Error %d: %s" % (e.args[0], e.args[1]))
            return False

    def select_limit(self, sql='', offset=0, length=20):
        sql = '%s limit %d , %d ;' % (sql, offset, length)
        return self.select(sql)

    def query(self, sql=''):
        try:
            self._reConn()
            self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
            self.cursor.execute("set names utf8")  # utf8 字符集
            result = self.cursor.execute(sql)
            self.conn.commit()
            self.cursor.close()
            return (True, result)
        except MySQLdb.Error, e:
            # self.logger.error("Error %d: %s" % (e.args[0], e.args[1]))
            return False

    def close(self):
        self.conn.close()


class MySQLTransaction(object):
    """MySQL事务
    """

    def __init__(self,
                 host='127.0.0.1',
                 user='fy',
                 passwd='fy0123498765',
                 db='imcmdb',
                 port=3306,
                 charset='utf8'
                 ):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port
        self.charset = charset
        self.conn = None
        self.logger = logger()
        self._conn()
        self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)

    def _conn(self):
        try:
            self.conn = MySQLdb.Connection(self.host,
                                           self.user,
                                           self.passwd,
                                           self.db,
                                           self.port,
                                           self.charset)
            return True
        except:
            return False

    # 重试连接总次数为3次
    def _reConn(self, num=3, stime=1):
        _number = 0
        _status = True
        while _status and _number <= num:
            try:
                self.conn.ping()  # cping 校验连接是否异常
                _status = False
            except:
                if self._conn() is True:  # 重新连接,成功退出
                    _status = False
                    break
                _number += 1
                time.sleep(stime)  # 连接不成功,休眠1秒钟,继续循环，直到成功或重试次数结束

    def select(self, sql=''):
        try:
            self._reConn()
            self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
            self.cursor.execute("set names utf8")  # utf8 字符集
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            return result
        except MySQLdb.Error, e:
            self.logger.error("Error %d: %s" % (e.args[0], e.args[1]))
            return False

    def query(self, sql=''):
        try:
            self._reConn()
            self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
            self.cursor.execute("set names utf8")  # utf8 字符集
            result = self.cursor.execute(sql)
            return (True, result)
        except MySQLdb.Error, e:
            self.logger.error("Error %d: %s" % (e.args[0], e.args[1]))
            return False

    def commit(self):
        """提交事务
        """
        self.cursor.close()
        self.conn.commit()

    def rollback(self):
        """回滚事务
        """
        self.cursor.close()
        self.conn.rollback()

    def close(self):
        self.conn.close()
